package gov.center.utils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ReadExcelUtil {
	private Logger logger = LoggerFactory.getLogger(ReadExcelUtil.class);
	private Workbook wb;
	private Sheet sheet;
	private Row row;
	private InputStream is;

	/**
	 * 初始化excel文件
	 * 
	 * @param filepath
	 */
	public ReadExcelUtil(String filepath) {
		if (filepath == null) {
			return;
		}
		String ext = filepath.substring(filepath.lastIndexOf("."));
		try {
			 is = new FileInputStream(filepath);
			if (".xls".equals(ext)) {
				wb = new HSSFWorkbook(is);
			} else if (".xlsx".equals(ext)) {
				wb = new XSSFWorkbook(is);
			} else {
				wb = null;
			}
		} catch (FileNotFoundException e) {
			logger.error("FileNotFoundException", e);
		} catch (IOException e) {
			logger.error("IOException", e);
		}finally {
			if(is!=null){
				try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}	
			}
		}
	}

	/**
	 * 读取Excel表格表头的内容
	 * 
	 * @param InputStream
	 * @return String 表头内容的数组
	 * @author zengwendong
	 */
	public String[][] readExcelTitle() throws Exception {
		if (wb == null) {
			throw new Exception("excel文件初始化失败！");
		}
		String[][] title = new String[wb.getNumberOfSheets()][getMaxColNum()];
		for (int i = 0; i < wb.getNumberOfSheets(); i++) {
			sheet = wb.getSheetAt(i);
			row = sheet.getRow(0);
			// 标题总列数
			int colNum = row.getPhysicalNumberOfCells();
			System.out.println("colNum:" + colNum);
			for (int j = 0; j < colNum; j++) {
				// title[i] = getStringCellValue(row.getCell((short) i));
				title[i][j] = row.getCell(j).getStringCellValue();
			}
		}
		return title;
	}

	/**
	 * 获取所有sheet中最大的列数
	 * 
	 * @return
	 */
	public int getMaxColNum() {
		int max = 0;
		for (int i = 0; i < wb.getNumberOfSheets(); i++) {
			sheet = wb.getSheetAt(i);
			row = sheet.getRow(0);
			int colNum = row.getPhysicalNumberOfCells();
			if (max < colNum) {
				max = colNum;
			}
		}
		return max;
	}

	/**
	 * 读取Excel数据内容
	 * 
	 * @param InputStream
	 * @return Map 包含所有sheet里面的数据
	 * @author zengwendong
	 */
	public Map<Integer, ArrayList<ArrayList<Object>>> readExcelContent() throws Exception {
		if (wb == null) {
			throw new Exception("excel文件初始化失败！");
		}
		Map<Integer, ArrayList<ArrayList<Object>>> Allcontent = new HashMap<Integer, ArrayList<ArrayList<Object>>>();
	
		for (int k = 0; k < wb.getNumberOfSheets(); k++) {
			ArrayList<ArrayList<Object>> content = new ArrayList<ArrayList<Object>>();
			sheet = wb.getSheetAt(k);
			// 得到总行数
			int rowNum = sheet.getLastRowNum();
			System.out.println("总行数"+rowNum);
			//得到物理行数
//			 rowNum = sheet.getPhysicalNumberOfRows();
//			 System.out.println("物理行数"+rowNum);
			// 得到总列数
			// 正文内容应该从第二行开始,第一行为表头的标题
			for (int i = 1; i <= rowNum; i++) {	
				if(sheet.getRow(i)==null){
					continue;
				}
				row = sheet.getRow(i);		
				int colNum = row.getPhysicalNumberOfCells();
				int j = 0;
				ArrayList<Object> cellValue = new ArrayList<Object>();
				while (j <= colNum) {		
					if(row.getCell(j)==null || row.getCell(j).equals("")){
						cellValue.add("");	
					}		
					else{
						 Object obj = getCellFormatValue(row.getCell(j));
						 cellValue.add(obj);	
					}
					j++;
				}
				content.add(cellValue);
			}
			Allcontent.put(k, content);
		}
	
		return Allcontent;
	}

	/**
	 * 
	 * 根据Cell类型设置数据
	 * 
	 * @param cell
	 * @return
	 * @author zengwendong
	 */
	private Object getCellFormatValue(Cell cell) {
		Object cellvalue = "";
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_NUMERIC://数字
				  //如果为时间格式的内容
		          if (DateUtil.isCellDateFormatted(cell)) {      
		             //注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
		             SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
		             cellvalue=sdf.format(DateUtil.getJavaDate(cell.
		             getNumericCellValue())).toString();                                 
		               break;
		           } else {
		        	   long longVal = Math.round(cell.getNumericCellValue());  
		        	    if (Double.parseDouble(longVal + ".0") == cell.getNumericCellValue())  
		        	    	cellvalue = longVal;  
		        	    else  
		        	    	 cellvalue = new DecimalFormat("#.#").format(cell.getNumericCellValue());     	  
		           }
		          break;// 如果当前Cell的Type为NUMERIC
			case Cell.CELL_TYPE_FORMULA: {
				// 判断当前的cell是否为Date
				if (DateUtil.isCellDateFormatted(cell)) {
					// 如果是Date类型则，转化为Data格式
					// data格式是带时分秒的：2013-7-10 0:00:00
					// cellvalue = cell.getDateCellValue().toLocaleString();
					// data格式是不带带时分秒的：2013-7-10
					Date date = cell.getDateCellValue();
					cellvalue = date;
				} else {// 如果是纯数字

					// 取得当前Cell的数值
					cellvalue = String.valueOf(cell.getNumericCellValue());
				}
				break;
			}
			case Cell.CELL_TYPE_STRING:// 如果当前Cell的Type为STRING
				// 取得当前的Cell字符串
				cellvalue = cell.getRichStringCellValue().getString();
				break;
			default:// 默认的Cell值
				cellvalue = "";
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;
	}

	//测试
	public static void main(String[] args) {
		
		try {
			ReadExcelUtil excelUtil = 	new ReadExcelUtil("E:/导入relevance.xlsx");//读取excle文件进入内存
			String title[][]=excelUtil.readExcelTitle();
			Map<Integer,ArrayList<ArrayList<Object>>> test = excelUtil.readExcelContent();		
			ArrayList<ArrayList<Object>> sheet0 = test.get(0);
			for(ArrayList<Object> rows:sheet0){
				for(Object value:rows){
					System.out.print(value+" ,");
				}
				System.out.println("");
			}
		
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
}
